package server.cmd;

import java.sql.Connection;
import java.util.ArrayList;

import server.dao.TbExpenseDAO;
import server.dao.TbProcessDAO;
import server.vo.TbExpenseVO;
import server.vo.TbProcessVO;

public class TbProcessCmd {
	
	public String inqrBalanceList(Connection conn, String user_code, String start_date, String end_date, String pjt_code, String acnt_code) throws Exception {
		
		//Connection conn_1 = conn_param;
		TbProcessVO tbProcessVO = new TbProcessVO();
		TbProcessDAO tbProcessDAO = new TbProcessDAO();
		String szMsg ="";

		tbProcessVO.setUser_code(user_code);
		tbProcessVO.setStart_date(start_date);
		tbProcessVO.setEnd_date(end_date);
		if (pjt_code == null || "".equals(pjt_code)){
			tbProcessVO.setPjt_code("%");
		}else{
			tbProcessVO.setPjt_code(pjt_code);	
		}
		if (acnt_code == null || "".equals(acnt_code)){
			tbProcessVO.setAcnt_code("%");
		}else{
			tbProcessVO.setAcnt_code(acnt_code);	
		}
		
		
		try {
		
			ArrayList<TbProcessVO> al = tbProcessDAO.inqrBalanceList(conn, tbProcessVO);
			for (int i=0; i<al.size(); i++)
			{
				tbProcessVO = al.get(i);
				szMsg += tbProcessVO.getProc_date()+"|";
				szMsg += tbProcessVO.getPjt_code()+"|";
				szMsg += tbProcessVO.getPjt_name()+"|";
				szMsg += tbProcessVO.getAcnt_code()+"|";
				szMsg += tbProcessVO.getAcnt_name()+"|";
				szMsg += tbProcessVO.getBill_amount()+"|";
				szMsg += tbProcessVO.getProcess_code()+"^";
			}
		
			return szMsg;	
			
		} catch(Exception e) {
			throw e;
		}	
	}
	
	public String inqrBalanceListByDate(Connection conn, String start_date, String end_date, String user_code) throws Exception {
		
		//Connection conn_1 = conn_param;
		TbProcessVO tbProcessVO = new TbProcessVO();
		TbProcessDAO tbProcessDAO = new TbProcessDAO();
		String szMsg ="";

		tbProcessVO.setStart_date(start_date);
		tbProcessVO.setEnd_date(end_date);
		tbProcessVO.setUser_code(user_code);
		
		try {
		
			ArrayList<TbProcessVO> al = tbProcessDAO.inqrBalanceListByDate(conn, tbProcessVO);
			for (int i=0; i<al.size(); i++)
			{
				tbProcessVO = al.get(i);
				szMsg += tbProcessVO.getProc_date()+"|";
				szMsg += tbProcessVO.getPjt_code()+"|";
				szMsg += tbProcessVO.getPjt_name()+"|";
				szMsg += tbProcessVO.getAcnt_code()+"|";
				szMsg += tbProcessVO.getAcnt_name()+"|";
				szMsg += tbProcessVO.getBill_amount()+"^";
			}
		
			return szMsg;	
			
		} catch(Exception e) {
			throw e;
		}	
	}
	
	
	public String inqrBalanceListByPjt(Connection conn, String pjt_code) throws Exception {
		
		//Connection conn_1 = conn_param;
		TbProcessVO tbProcessVO = new TbProcessVO();
		TbProcessDAO tbProcessDAO = new TbProcessDAO();
		String szMsg ="";

		tbProcessVO.setPjt_code(pjt_code);
		
		try {
		
			ArrayList<TbProcessVO> al = tbProcessDAO.inqrBalanceListByPjt(conn, tbProcessVO);
			for (int i=0; i<al.size(); i++)
			{
				tbProcessVO = al.get(i);
				szMsg += tbProcessVO.getProc_date()+"|";
				szMsg += tbProcessVO.getPjt_code()+"|";
				szMsg += tbProcessVO.getPjt_name()+"|";
				szMsg += tbProcessVO.getAcnt_code()+"|";
				szMsg += tbProcessVO.getAcnt_name()+"|";
				szMsg += tbProcessVO.getBill_amount()+"^";
			}
		
			return szMsg;	
			
		} catch(Exception e) {
			throw e;
		}	
	}
	
	public String inqrBalanceListByAcnt(Connection conn, String acnt_code, String user_code) throws Exception {
		
		//Connection conn_1 = conn_param;
		TbProcessVO tbProcessVO = new TbProcessVO();
		TbProcessDAO tbProcessDAO = new TbProcessDAO();
		String szMsg ="";

		tbProcessVO.setAcnt_code(acnt_code);
		tbProcessVO.setUser_code(user_code);
		
		try {
		
			ArrayList<TbProcessVO> al = tbProcessDAO.inqrBalanceListByAcnt(conn, tbProcessVO);
			
			for (int i=0; i<al.size(); i++)
			{
				tbProcessVO = al.get(i);
				szMsg += tbProcessVO.getProc_date()+"|";
				szMsg += tbProcessVO.getPjt_code()+"|";
				szMsg += tbProcessVO.getPjt_name()+"|";
				szMsg += tbProcessVO.getAcnt_code()+"|";
				szMsg += tbProcessVO.getAcnt_name()+"|";
				szMsg += tbProcessVO.getBill_amount()+"|";
				szMsg += tbProcessVO.getUser_code()+"|";
				szMsg += tbProcessVO.getUser_name()+"|";
				szMsg += tbProcessVO.getAbstract()+"|";
				szMsg += tbProcessVO.getBp()+"|";
				szMsg += tbProcessVO.getPurpose()+"^";
						
			}
		
			return szMsg;	
			
		} catch(Exception e) {
			throw e;
		}	
	}
	
	public String updateBalanceList(Connection conn, String pjt_code, String acnt_code, String process_code, String bill_amount) throws Exception {
		
		//Connection conn_1 = conn_param;
		TbProcessVO tbProcessVO = new TbProcessVO();
		TbProcessVO respProcessVO = new TbProcessVO();
		TbProcessDAO tbProcessDAO = new TbProcessDAO();
		TbExpenseDAO tbExpenseDAO = new TbExpenseDAO();
		TbExpenseVO respExpenseVO =new TbExpenseVO();
		TbExpenseVO tbExpenseVO =new TbExpenseVO();
		String szMsg ="SUCCESS";

		tbProcessVO.setPjt_code(pjt_code);
		tbProcessVO.setAcnt_code(acnt_code);
		tbProcessVO.setProcess_code(process_code);
		
		try {
			// =========================
			// ��뿹��TB���� �ܾ� �ݿ�
			// =========================
			
			respProcessVO = tbProcessDAO.inqrTbProcessBill(conn, tbProcessVO);
			tbExpenseVO.setPrt_code(respProcessVO.getPjt_code());
			tbExpenseVO.setAcnt_code(respProcessVO.getAcnt_code());
			
			respExpenseVO = tbExpenseDAO.inqrAcntBalance(conn, tbExpenseVO);
			int acntBalance = respExpenseVO.getAcnt_balance();
			
			// ���� ������Ʈ/������ �ܾ�+
			acntBalance = acntBalance + Integer.parseInt(bill_amount);
			tbExpenseVO.setAcnt_balance(acntBalance);
			boolean results = tbExpenseDAO.updateAcntBalance(conn, tbExpenseVO);
			if(!results){
				throw new Exception("���� �� ���� �߻��Ͽ����ϴ�.");				
			}
			
			// ����� ������Ʈ/������ �ܾ�-
			tbExpenseVO.setPrt_code(pjt_code);
			tbExpenseVO.setAcnt_code(acnt_code);
			
			respExpenseVO = tbExpenseDAO.inqrAcntBalance(conn, tbExpenseVO);
			
			acntBalance = respExpenseVO.getAcnt_balance();
			acntBalance = acntBalance - Integer.parseInt(bill_amount);
			tbExpenseVO.setAcnt_balance(acntBalance);
			results = tbExpenseDAO.updateAcntBalance(conn, tbExpenseVO);
			if(!results){
				throw new Exception("���� �� ���� �߻��Ͽ����ϴ�.");
			}
			
			// =========================
			// �������TB �ݿ�
			// =========================
			results = tbProcessDAO.updateBalanceList(conn, tbProcessVO);	
			if(!results){
				throw new Exception("���� �� ���� �߻��Ͽ����ϴ�.");
			}
				
			
			return szMsg;	
			
		} catch(Exception e) {
			throw e;
		}	
	}
	
	public String deleteBalanceList(Connection conn, String process_code_list) throws Exception {
		
		//Connection conn_1 = conn_param;
		TbProcessVO tbProcessVO = new TbProcessVO();
		TbProcessDAO tbProcessDAO = new TbProcessDAO();
		TbProcessVO respProcessVO = new TbProcessVO();
		TbExpenseDAO tbExpenseDAO = new TbExpenseDAO();
		TbExpenseVO respExpenseVO =new TbExpenseVO();
		TbExpenseVO tbExpenseVO =new TbExpenseVO();
		String szMsg ="SUCCESS";

		String processCode[] = process_code_list.split("^");	// ������û�� ������ ���� ��� �����ڸ� ^�� ��
		
		try {
			for (int pcode = 0; pcode < processCode.length; pcode++){
				
				// =========================
				// ��뿹��TB �ݿ�
				// =========================
				// ������Ʈ/������ �ܾ�+								
				tbProcessVO.setProcess_code(processCode[pcode]);
				respProcessVO = tbProcessDAO.inqrTbProcessBill(conn, tbProcessVO);
				
				tbExpenseVO.setPrt_code(respProcessVO.getPjt_code());
				tbExpenseVO.setAcnt_code(respProcessVO.getAcnt_code());
				respExpenseVO = tbExpenseDAO.inqrAcntBalance(conn, tbExpenseVO);
				int acntBalance = respExpenseVO.getAcnt_balance();
				
				acntBalance = acntBalance + respProcessVO.getBill_amount();
				tbExpenseVO.setAcnt_balance(acntBalance);
				boolean results = tbExpenseDAO.updateAcntBalance(conn, tbExpenseVO);
				if(!results){
					throw new Exception("���� �� ���� �߻��Ͽ����ϴ�.");
				}				
				
				// =========================
				// �������TB �ݿ�
				// =========================
				results = tbProcessDAO.deleteBalanceList(conn, tbProcessVO);	
				if(!results){
					throw new Exception("���� �� ���� �߻��Ͽ����ϴ�.");
				}
			}
			
			return szMsg;	
			
		} catch(Exception e) {
			throw e;
		}	
	}
	
	public String insertBalanceList(Connection conn, String proc_date, String pjt_code, String acnt_code, String bill_amount, String bill_code) throws Exception {
		
		TbProcessVO tbProcessVO = new TbProcessVO();
		TbProcessVO respProcessVO = new TbProcessVO();
		TbExpenseVO tbExpenseVO =new TbExpenseVO();
		TbExpenseVO respExpenseVO =new TbExpenseVO();
		TbProcessDAO tbProcessDAO = new TbProcessDAO();
		TbExpenseDAO tbExpenseDAO = new TbExpenseDAO();
		String szMsg ="SUCCESS";

		tbProcessVO.setProc_date(proc_date);
		tbProcessVO.setPjt_code(pjt_code);
		tbProcessVO.setAcnt_code(acnt_code);
		tbProcessVO.setBill_amount(Integer.parseInt(bill_amount));
		tbProcessVO.setBill_code(bill_code);
		
		try {
			// =========================
			// ��뿹��TB���� �ܾ� �ݿ�
			// =========================
			tbExpenseVO.setPrt_code(pjt_code);
			tbExpenseVO.setAcnt_code(acnt_code);
			respExpenseVO = tbExpenseDAO.inqrAcntBalance(conn, tbExpenseVO);
			int acntBalance = respExpenseVO.getAcnt_balance();
			
			acntBalance = acntBalance - Integer.parseInt(bill_amount);
			tbExpenseVO.setAcnt_balance(acntBalance);
			boolean results = tbExpenseDAO.updateAcntBalance(conn, tbExpenseVO);
			if(!results){
				throw new Exception("��� �� ���� �߻��Ͽ����ϴ�.");
			}
			
			// =========================
			// �������TB �ݿ�
			// =========================
			// process_codeä��
			String maxpCode = tbProcessDAO.inqrMaxProcessCode(conn, tbProcessVO);
			tbProcessVO.setProcess_code(maxpCode);
			
			//insert
			results = tbProcessDAO.insertBalanceList(conn, tbProcessVO);

			if(!results){
				throw new Exception("��� �� ���� �߻��Ͽ����ϴ�.");
			}
				
			
			return szMsg;	
			
		} catch(Exception e) {
			throw e;
		}	
	}
}
